package org.jeecg.modules.test;



import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.common.exception.JeecgBootException;

import java.io.*;
import java.util.Arrays;
import java.util.List;

public class ExcelWriterJava8ExactSize2 {

    // 1cm = 28.35 points，这是Excel中高度的单位
    private static final float CM_TO_POINTS = 28.35f;
    // 1列宽单位 ≈ 0.145 cm，这是用于计算列宽的经验值
    private static final float CM_TO_EXCEL_COLUMN_WIDTH = 0.145f;
    // 1点 (point) = 9525 EMUs (English Metric Units)，用于精确设置图片位置
    private static final int EMUS_PER_POINT = 9525;


    public static void main(String[] args) throws Exception {
        // 请根据你的实际路径修改
        String templatePath = "/Users/blithe/Desktop/工程服务验收单-模版.xlsx";
        String outputPath = "/Users/blithe/dev_tools/work/crm/jeecg-boot/jeecg-module-demo/src/main/java/org/jeecg/modules/test/输出文件.xlsx";

        String serviceUnit = "上海南裕建筑装饰工程有限公司";
        String serviceProject = "2025/7/31 宜兴仓 Zimmer项目新增100组小货架";
        String projectNo = "SCM2025062400777";
        String date = "2025年07月31日";

        List<String> imagePaths = Arrays.asList(
               /* "/Users/blithe/Desktop/WechatIMG2637.jpg",
                "/Users/blithe/Desktop/WechatIMG1126.jpg",*/
                "/Users/blithe/Desktop/1111需求.jpg",
                "/Users/blithe/Desktop/11111111WechatIMG145.jpg",
                "/Users/blithe/Desktop/1111需求.jpg",
                "/Users/blithe/Desktop/WechatIMG11351.jpg",
                "/Users/blithe/Downloads/【哲风壁纸】凯蒂猫-可爱-富士山.png",
                "/Users/blithe/Downloads/【哲风壁纸】办公场景-励志文字.png",
                "/Users/blithe/Downloads/【哲风壁纸】凯蒂猫-可爱-富士山.png",
                "/Users/blithe/Downloads/【哲风壁纸】办公场景-励志文字.png",
                "/Users/blithe/Downloads/【哲风壁纸】凯蒂猫-可爱-富士山.png",
                "/Users/blithe/Downloads/【哲风壁纸】办公场景-励志文字.png",
                "/Users/blithe/Downloads/【哲风壁纸】咖啡-咖啡店-夜景.png",
                "/Users/blithe/Downloads/【哲风壁纸】咖啡-咖啡店-夜景.png"
        );

        FileInputStream fis = new FileInputStream(templatePath);
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        String serviceContentText = "本次服务内容包括：\n1. 服务器硬件巡检。\n2. 系统日志检查与清理。\n3. 安全补丁更新。";


        // 设置字体为 SimSun，字号为 11
        Font font = workbook.createFont();
        font.setFontName("SimSun");
        font.setFontHeightInPoints((short) 11);

        // 创建单元格样式，包括字体、垂直居中和边框
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        cellStyle.setBorderTop(BorderStyle.THIN); // 上边框
        cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); // 左边框
        cellStyle.setBorderRight(BorderStyle.THIN); // 右边框

        // 写入文字
        sheet.getRow(1).getCell(1).setCellValue(serviceUnit);    // B2
        sheet.getRow(2).getCell(1).setCellValue(serviceProject); // B3
        sheet.getRow(3).getCell(1).setCellValue(projectNo);      // B4
        sheet.getRow(4).getCell(1).setCellValue(serviceContentText);

        String serverText = "服务单位" +
                "\n" +
                "\n" +
                "\n                  "+date;
        sheet.getRow(7).getCell(3).setCellValue(serverText);           // D9


        int perRow = 2;
        float widthCm = 7f;
        float heightCm= 7f;
        if(imagePaths.size()> 12){
            throw new JeecgBootException("文件超过12个，请手动处理");
        }


        // 插入图片（从B5开始，每行4张，4cm×4cm）
        // 这里调用的insertImagesExact方法会处理行高和列宽的设置
        insertImagesExact(sheet, imagePaths, 5, 1, workbook, perRow, widthCm, heightCm);

        fis.close();

        FileOutputStream fos = new FileOutputStream(outputPath);
        workbook.write(fos);
        fos.close();
        workbook.close();

        System.out.println("Excel 生成完成：" + outputPath);
    }

    /**
     * 在Excel中插入图片并严格控制尺寸，同时根据图片数量自动调整行高
     * @param sheet Excel工作表对象
     * @param imagePaths 图片路径列表
     * @param startRow 起始行（Excel索引，从0开始，即第6行）
     * @param startCol 起始列（Excel索引，从0开始，即B列）
     * @param workbook 工作簿对象
     * @param perRow 每行显示的图片数量
     * @param widthCm 图片宽度（单位：厘米）
     * @param heightCm 图片高度（单位：厘米）
     */
    private static void insertImagesExact(Sheet sheet, List<String> imagePaths, int startRow, int startCol,
                                          XSSFWorkbook workbook, int perRow, float widthCm, float heightCm) throws IOException {
        XSSFDrawing drawing = (XSSFDrawing) sheet.createDrawingPatriarch();

        // 高度计算：将厘米转换为Excel的高度单位（磅）
        float rowHeightPoints = CM_TO_POINTS * heightCm;
        // 宽度计算：将厘米转换为Excel的列宽单位（1/256个字符宽度）
        int excelColWidthUnits = (int) (widthCm / CM_TO_EXCEL_COLUMN_WIDTH);

        // 设置对应列宽
        for (int i = 0; i < perRow; i++) {
            sheet.setColumnWidth(startCol + i, excelColWidthUnits * 256);
        }

        // 计算所有图片所需的总行数，用于设置第6行的总高度
        int totalImageRows = (int) Math.ceil(imagePaths.size() / (double) perRow);
        float totalHeightPoints = totalImageRows * rowHeightPoints;

        // 设置起始行（第6行）的高度
        Row excelRow = sheet.getRow(startRow);
        if (excelRow == null) {
            excelRow = sheet.createRow(startRow);
        }
        excelRow.setHeightInPoints(totalHeightPoints);

        int colOffset = 0;
        int rowOffset = 0;

        for (String imgPath : imagePaths) {
            InputStream is = new FileInputStream(imgPath);
            byte[] bytes = readAllBytes(is);
            is.close();

            int pictureIdx;
            if (imgPath.toLowerCase().endsWith(".png")) {
                pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
            } else {
                pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
            }

            int col = startCol + colOffset;

            // 计算图片在第6行内的垂直偏移量
            int yOffsetEmu = (int) (rowOffset * rowHeightPoints * EMUS_PER_POINT);
            int yEndOffsetEmu = (int) ((rowOffset + 1) * rowHeightPoints * EMUS_PER_POINT);

            // 创建一个客户端锚点，用于定位和调整图片大小
            XSSFClientAnchor anchor = new XSSFClientAnchor();
            // 将所有图片都锚定在同一行 (startRow) 内
            anchor.setCol1(col);
            anchor.setRow1(startRow);
            anchor.setCol2(col + 1);
            anchor.setRow2(startRow);
            // 通过 dy1 和 dy2 精确控制图片在垂直方向上的位置
            anchor.setDy1(yOffsetEmu);
            anchor.setDy2(yEndOffsetEmu);
            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);

            drawing.createPicture(anchor, pictureIdx);

            colOffset++;
            if (colOffset == perRow) { // 如果一行已经满了，就换到“虚拟”的下一行
                colOffset = 0;
                rowOffset++;
            }
        }
    }

    private static byte[] readAllBytes(InputStream is) throws IOException {
        ByteArrayOutputStream buffer = new ByteArrayOutputStream();
        byte[] data = new byte[1024];
        int nRead;
        while ((nRead = is.read(data, 0, data.length)) != -1) {
            buffer.write(data, 0, nRead);
        }
        buffer.flush();
        return buffer.toByteArray();
    }


}
